package org.iabako.server.dao.business;

import org.iabako.server.dao.JpaDAO;
import org.iabako.server.tools.ServerTools;
import org.iabako.shared.entity.business.*;
import org.iabako.shared.entity.enumeration.FinancialStatusSale;
import org.iabako.shared.tools.GenericTools;
import org.springframework.stereotype.Repository;

import javax.persistence.Query;
import java.util.Date;
import java.util.List;

/**
 * Created by jose on 26/08/14.
 */

@Repository("saleDAO")
public class SaleDAO extends JpaDAO<Long, Sale> {

    @Override
    protected String getUniqueNumberVerificationQuery() {
        return "SELECT s.id FROM Sale s WHERE s.number = :number AND s.enterprise IN (:enterprises)";
    }

    @Override
    protected String getLastNumberQuery() {
        return "SELECT MAX(s.number) FROM Sale s WHERE s.enterprise IN (:enterprises) AND s.quote = 0";
    }

    public void cleanProductService(Sale saleFromDB) {
        if (saleFromDB.getId() == null) {
            //This sale doesn't come from DB
            return;
        }
        String queryString = "DELETE FROM SaleProductService prodSer WHERE prodSer.sale = :sale";
        entityManager.createQuery(queryString).setParameter("sale", saleFromDB).executeUpdate();
    }

    public void deleteOrphanPayments(Sale saleFromDB) {
        String queryString = "DELETE FROM Payment payment WHERE payment.sale = :sale";
        Query query = entityManager.createQuery(queryString);
        if (!GenericTools.isEmpty(saleFromDB.getPayments())) {
            queryString += " AND payment NOT IN (:payments)";
            query = entityManager.createQuery(queryString).setParameter("payments", saleFromDB.getPayments());
        }
        query.setParameter("sale", saleFromDB);
        query.executeUpdate();
    }

    public void deleteOrphanInstallments(Sale saleFromDB) {
        String queryString = "DELETE FROM Installment installment WHERE installment.sale = :sale";
        Query query = entityManager.createQuery(queryString);
        if (!GenericTools.isEmpty(saleFromDB.getInstallments())) {
            queryString += " AND installment NOT IN (:installments)";
            query = entityManager.createQuery(queryString).setParameter("installments", saleFromDB.getInstallments());
        }
        query.setParameter("sale", saleFromDB);
        query.executeUpdate();

    }

    public List<Installment> getCustomInstallmentsDefinition(Sale sale) {
        String query = "SELECT install FROM Installment install WHERE install.installmentsDefinition = :installmentsDefinition";
        return (List<Installment>) entityManager.createQuery(query).setParameter("installmentsDefinition", sale.getInstallmentsDefinition()).getResultList();
    }

    public List<Payment> getPayments(Sale sale) {
        String query = "SELECT payment FROM Payment payment WHERE payment.sale = :sale ORDER BY payment.date";
        return (List<Payment>) entityManager.createQuery(query).setParameter("sale", sale).getResultList();
    }

    public List<Installment> getInstallments(Sale sale) {
        String query = "SELECT install FROM Installment install WHERE install.sale = :sale ORDER BY install.date";
        return (List<Installment>) entityManager.createQuery(query).setParameter("sale", sale).getResultList();
    }

    public List<SaleProductService> getProductServiceList(Sale sale) {
        String query = "SELECT sps FROM SaleProductService sps WHERE sps.sale = :sale";
        return (List<SaleProductService>) entityManager.createQuery(query).setParameter("sale", sale).getResultList();
    }

    public int updateStatus() {
        String query = "UPDATE Sale sale SET sale.status = '_1_inDebt' WHERE sale.status <> '_1_inDebt' AND sale IN (SELECT DISTINCT i.sale FROM Installment i WHERE i.date <= :now)";
        return entityManager.createQuery(query).setParameter("now", new ServerTools().resetTime(new Date())).executeUpdate();
    }

    public List<InstallmentsDefinition> getExpiredUndefinedInstallments() {
        String query = "SELECT iDef FROM InstallmentsDefinition iDef WHERE iDef IN (SELECT DISTINCT iDef2 FROM Sale s JOIN s.installmentsDefinition iDef2) AND iDef.nextUndefinedInstallment <= :now";
        return (List<InstallmentsDefinition>) entityManager.createQuery(query).setParameter("now", new ServerTools().resetTime(new Date())).getResultList();
    }

    public void deleteAttachedObjects(Long id) {
        String query = "DELETE FROM TrackingObject to WHERE to.sale.id = :saleId";
        entityManager.createQuery(query).setParameter("saleId", id).executeUpdate();

        //This is for Sale delete (will update one Quote)
        query = "UPDATE Sale sale SET sale.saleQuoteId = NULL, sale.status = :quoteDraftStatus, sale.lastModificationDate = :now " +
                "WHERE (sale.saleQuoteId = :saleId OR sale.saleQuoteId = :saleDraftId) AND sale.quote = 1";
        entityManager.createQuery(query)
                .setParameter("saleId", id + "")
                .setParameter("saleDraftId", "_draft_" + id)
                .setParameter("quoteDraftStatus", FinancialStatusSale._0_quote_draft)
                .setParameter("now", new Date()).executeUpdate();

        //This is for Quote delete (will update one Sale)
        query = "UPDATE Sale sale SET sale.saleQuoteId = NULL, sale.lastModificationDate = :now " +
                "WHERE sale.saleQuoteId = :saleId AND sale.quote = 0";
        entityManager.createQuery(query)
                .setParameter("saleId", id + "")
                .setParameter("now", new Date()).executeUpdate();
    }
}
